/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package escom.ipn.modelo.dao;

import escom.ipn.modelo.dto.CRPClass;



import escom.ipn.modelo.dto.RadioClass;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;


import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author db2admin
 */
public class adminCRPDAO  {
    
    public static final String SQL_INSERT =
            "insert into carro_radio_patrulla(nombre,"
                              + "RADIO_COMUNICACION_id_radio,"
                              + "placas,"
                              + "asignado,"
                              + "SECTOR_id_sector,"
                              + "descripcion) "
                              + "values(?,?,?,?,?,?) ";
    
     public static final String SQL_UPDATE =
             "update carro_radio_patrulla set RADIO_COMUNICACION_id_radio=?,"
                              + "placas=?,"
                              + "nombre=?,"
                              + "sector_id_sector=?,"
                              + "descripcion=?"
                              + " where id_unidad=?";
     
    public static final String SQL_SELECT_ALL =
            "select * from carro_radio_patrulla";
    
    private static final String SQL_DELETE =
            "delete from carro_radio_patrulla where id_unidad = ?";
    
    public static final String SQL_SELECT =
            "select * from carro_radio_patrulla where id_unidad=? ";
    
    public static final String SQL_CRP_NOMBRES =
            "select nombre from carro_radio_patrulla where asignado<2";
    
    public static final String SQL_CRP_ID="select id_unidad from carro_radio_patrulla where nombre=?";
     
     public static final String SQL_CRP_NOMBRE="select nombre from carro_radio_patrulla where id_unidad=?";
     
     public static final String SQL_CRP_ASIGNACION="update carro_radio_patrulla set asignado=? where id_unidad=?";
    
    
     public void insertCRP(CRPClass a, Connection conn)
            throws SQLException {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(SQL_INSERT);
            ps.setString(1, a.getNombre());
            ps.setInt(2, a.getRADIO_COMUNICACION_id_radio());
            ps.setString(3, a.getPlacas());
            ps.setInt(4, 0);
            ps.setInt(5, a.getSector());
            ps.setString(6, a.getDescripcion());
            ps.executeUpdate();
        } 
        finally {
            cerrar(ps);
            cerrar(conn);
        } 
     }
    
    public List loadAll(Connection conn)
            throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(SQL_SELECT_ALL);
            rs = ps.executeQuery();
            List resultados = getResults(rs);
            if (resultados.size() > 0) {
                return resultados;
            } else {
                return null;
            }
        } finally {
            cerrar(rs);
            cerrar(ps);
            cerrar(conn);
        }
    }
    public void updateCRP(CRPClass a, Connection cnn) 
            throws SQLException {
         PreparedStatement ps = null;
        try {
            ps = cnn.prepareStatement(SQL_UPDATE);
            ps.setInt(1, a.getRADIO_COMUNICACION_id_radio());
            ps.setString(2, a.getPlacas());
            ps.setString(3, a.getNombre());
            ps.setInt(4, a.getSector());
            ps.setString(5, a.getDescripcion());
            ps.setInt(6, a.getId_carro_radio_patrulla());
            ps.executeUpdate();

        } finally {
            cerrar(ps);
            cerrar(cnn);
        } 
    }
    public List loadSelect(CRPClass a, Connection cnn) 
        throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = cnn.prepareStatement(SQL_SELECT);
            ps.setInt(1, a.getId_carro_radio_patrulla());
            rs = ps.executeQuery();
            List resultados = getResults(rs);
            if (resultados.size() > 0) {
                return resultados;
            } else {
                return null;
            }
        } finally {
            cerrar(rs);
            cerrar(ps);
            cerrar(cnn);
        }
    }
    
        public void borrarCRP(CRPClass a,Connection conn) 
        throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(SQL_DELETE);
            ps.setInt(1,a.getId_carro_radio_patrulla());
            ps.executeUpdate();
        } finally {
            cerrar(rs);
            cerrar(ps);
            cerrar(conn);
          
        }
   }
    
    
    
    private void cerrar(Connection cnn) {
        if (cnn != null) {
            try {
                cnn.close();
            } catch (SQLException e) {
                //manejo del error
            }
        }
    }

    private void cerrar(PreparedStatement ps) {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                //manejo del error
            }
        }
    }

    private void cerrar(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                //manejo del error
            }
        }
    }
    
    private List getResults(ResultSet rs)
            throws SQLException {
        List results = new ArrayList();
        while (rs.next()) {
            CRPClass a = new CRPClass();
            a.setId_carro_radio_patrulla(rs.getInt("id_unidad"));
            a.setRADIO_COMUNICACION_id_radio(rs.getInt("RADIO_COMUNICACION_id_radio"));
            a.setNombre(rs.getString("nombre"));
            a.setPlacas(rs.getString("placas"));
            a.setDescripcion(rs.getString("descripcion"));
            a.setAsignado(rs.getInt("asignado"));
            a.setSector(rs.getInt("sector_id_sector"));
            results.add(a);
        }
        return results;
    }
    
    
private List getResultsIDUnidad(ResultSet rs)
            throws SQLException {
        List results = new ArrayList();
        while (rs.next()) {
            CRPClass a = new CRPClass();
            a.setNombre(rs.getString("nombre"));
            results.add(a);
        }
        return results;
}
    
    public List getCRPs(Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(SQL_CRP_NOMBRES);
            rs = ps.executeQuery();
            List resultados = getResultsIDUnidad(rs);
            if (resultados.size() > 0) {
                return resultados;
            } else {
                return null;
            }
        } finally {
            cerrar(rs);
            cerrar(ps);
            cerrar(conn);
        }
    }
    
    public List getCRPID(CRPClass a, Connection cnn) throws SQLException{
        PreparedStatement ps = null;
        ResultSet rs = null;
        List results = new ArrayList();
        ps = cnn.prepareStatement(SQL_CRP_ID);
        ps.setString(1,a.getNombre());
        rs=ps.executeQuery();
        while(rs.next()){
            CRPClass b =new CRPClass();
            b.setId_carro_radio_patrulla(rs.getInt(1));
            results.add(b);
        }
        return results;              
        }
        
        public List getCRPNOMBRE(CRPClass a, Connection cnn) throws SQLException{
        PreparedStatement ps = null;
        ResultSet rs = null;
        List results = new ArrayList();
        ps = cnn.prepareStatement(SQL_CRP_NOMBRE);
        ps.setInt(1,a.getId_carro_radio_patrulla());
        rs=ps.executeQuery();
        while(rs.next()){
            CRPClass b =new CRPClass();
            b.setNombre(rs.getString(1));
            results.add(b);
        }
        return results;          
        }
        
        public void asignacion(CRPClass a, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ps = conn.prepareStatement(SQL_CRP_ASIGNACION);
        ps.setInt(1,a.getAsignado());
        ps.setInt(2,a.getId_carro_radio_patrulla());
        ps.executeUpdate();
        cerrar(ps);
        
        }

    
    
}
